from Staday.pyUntil import DBHelper

from Staday.write_excel import write
from Util.handle_excel import excel_data

excel_data_list = excel_data.get_excel_data(index=0)

db_helper = DBHelper()
sql = "select * from user"
user_result_list = list(db_helper.fetchAll(sql))

for user_result_one in user_result_list:
    if isinstance(user_result_one, tuple):
        user_result_list[user_result_list.index(user_result_one)] = list(user_result_one)
    else:
        pass

expenses_list = []

for excel_one in excel_data_list:
    for user in user_result_list:
        if excel_one[2] in user and excel_one != None:
            excel_one.append(user[0])
            excel_one.append(user[1])
            excel_one.append(user[3])
            excel_one.append(1)
            expenses_list.append(excel_one)

        else:
            pass

# sql2 = "INSERT INTO expenses_pay (id,userId,userName,name,typeId,bankUsername,bankName,bankNo,createTime,updateTime) VALUES
# (1,198,'17715243002','陶恺',	2,'11','中行','1111','2020-10-14 05:59:23',NULL),"

filename = 'demo_text.txt'

with open(filename, 'w') as file_object:
    for one in expenses_list:
        sql = "insert into expenses_pay (bankName,bankNo,bankUsername,userId,name,userName,typeId) values ({0});".format(
            one)
        sql_new = sql.replace("[", "").replace("]", "")

        file_object.writelines(sql_new + "\n")
file_object.close()
